﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data.OleDb;
using System.Data;
using System.Text.RegularExpressions;
using System.Data.SqlClient;
using System.Diagnostics;
using System.Configuration;
using System.IO;
using System.Collections;

namespace mpsalaryprojectgroup
{
    public partial class NEWAdminUploadResult : System.Web.UI.Page
    {
        
        protected void Page_Load(object sender, EventArgs e)
        {
            ImageButton3.Enabled = true;
           // ImageButton2.Enabled = true;

            //if there's a file selected, imagebutton can press
            if (uploadFileTextbox.HasFile)
            {
                ImageButton2.Enabled = true;
            }
            else
            {
                ImageButton2.Enabled = false;
                
            }

            String username = (string)(Session["UsenameLogin"]);
            if (username == null)
            {
                Response.Redirect("NEWMainPage.aspx");
            }
            else
            {
                lblUsername.Text = username;
            }
            DisableBufferingOnPage();

        }
        protected void DisableBufferingOnPage()
        {
            Response.Buffer = true;
            Response.ExpiresAbsolute = DateTime.Now.AddDays(-1);
            // set expiry date in the past
            Response.Expires = -1;
            Response.CacheControl = "no-cache";
            Response.Cache.SetNoStore();
            Response.AppendHeader("Pragma", "no-cache");
            Response.AppendHeader("Cache-Control", "no-cache");
            Response.CacheControl = "no-cache";
            Response.Expires = -1;
            Response.ExpiresAbsolute = new DateTime(1900, 1, 1);
            Response.Cache.SetCacheability(HttpCacheability.NoCache);
        }

        protected void btnUploadFile_Click1(object sender, EventArgs e)
        {
            PanelUpload.Visible = true;
            PanelView.Visible = false;

            LabelUpload.Text = "";
            lblImported.Text = "";
            uploadFileTextbox.Visible = true;
        }


        protected void ButtonUploadFile_Click(object sender, EventArgs e)
        {
            if (uploadFileTextbox.HasFile)
            {
                string fileExt =
              System.IO.Path.GetExtension(uploadFileTextbox.FileName);

                if (fileExt == ".xls")
                {

                    try
                    {
                        // alter path for your project

                        uploadFileTextbox.SaveAs(Server.MapPath("~/Saved.xls"));
                        LabelUpload.Text = "Upload File Name: " +
                        uploadFileTextbox.PostedFile.FileName +
                        "<br>" + "Type: " +
                        uploadFileTextbox.PostedFile.ContentType +
                        " File Size: " + uploadFileTextbox.PostedFile.ContentLength
                        + " kb<br>";
                    }
                    catch (System.NullReferenceException ex)
                    {
                        LabelUpload.Text = "Error: " + ex.Message;
                    }
                }
                else
                {
                    LabelUpload.Text = "Only .xls files allowed!";
                }

                ImageButton3.Visible = true;

            }
            else
            {
                LabelUpload.Text = "Please select a file to upload.";
            }
        }

        protected OleDbCommand ExcelConnection()
        {


            // Connect to the Excel Spreadsheet
            string connectionstring = "Provider=Microsoft.Jet.OLEDB.4.0;" +
            "Data Source=" + Server.MapPath("Saved.xls") + ";" +
            "Extended Properties=Excel 8.0;";

            // create your excel connection object using the connection string
            OleDbConnection Conn = new OleDbConnection(connectionstring);
            Conn.Open();

            // use a SQL Select command to retrieve the data from the Excel Spreadsheet
            // the "table name" is the name of the worksheet within the spreadsheet
            // in this case, the worksheet name is "2010" and is coded as: [2010$]

            OleDbCommand Comm = new OleDbCommand("SELECT * FROM [2010$]", Conn);
            return Comm;

        }

        public static String[] GetExcelSheetNames(string excelFile)
        {
            OleDbConnection objConn = null;
            System.Data.DataTable dt = null;

            try
            {
                // Connection String. Change the excel file to the file you will search.

                String connString = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + excelFile + ";Extended Properties=Excel 8.0;";
                // Create connection object by using the preceding connection string.

                objConn = new OleDbConnection(connString);
                // Open connection with the database.

                objConn.Open();
                // Get the data table containg the schema guid.

                dt = objConn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);

                if (dt == null)
                {
                    return null;
                }

                String[] excelSheets = new String[dt.Rows.Count];
                int i = 0;

                // Add the sheet name to the string array.

                foreach (DataRow row in dt.Rows)
                {
                    excelSheets[i] = row["TABLE_NAME"].ToString();
                    i++;
                }

                return excelSheets;
            }
            catch (Exception ex)
            {
                return null;
            }
            finally
            {
                // Clean up.

                if (objConn != null)
                {
                    objConn.Close();
                    objConn.Dispose();
                }
                if (dt != null)
                {
                    dt.Dispose();
                }
            }
        }

        //this method is to remove the $ sign that retrieve from the excel file eg, 2010$
        //so that it can save in the db at 2010 for year
        public static string RemoveSpecialCharacters(string[] input)
        {
            Regex r = new Regex("(?:[^a-z0-9 ]|(?<=['\"])s)", RegexOptions.IgnoreCase | RegexOptions.CultureInvariant | RegexOptions.Compiled);
            return r.Replace(input[0], String.Empty);
        }


        protected void ImageButton1_Click(object sender, ImageClickEventArgs e)
        {
            PanelUpload.Visible = true;
            PanelView.Visible = false;

            LabelUpload.Text = "";
            lblImported.Text = "";
            uploadFileTextbox.Visible = true;

            ImageButton2.Enabled = false;
            ImageButton3.Enabled = false;
            lblduplicate.Visible = false;
        }

        //view data on datagrid
        protected void ImageButton2_Click(object sender, ImageClickEventArgs e)
        {
            PanelUpload.Visible = false;
            PanelView.Visible = true;
            lblImported.Text = "";
            uploadFileTextbox.Visible = false;
            lblduplicate.Visible = false;


            String[] excelsheetname = GetExcelSheetNames(Server.MapPath("~/Saved.xls"));

            OleDbConnection conn = new OleDbConnection();
            conn.ConnectionString = @"Provider=Microsoft.ACE.OLEDB.12.0;" + "Data Source=" + Server.MapPath("~/Saved.xls") + @";Extended Properties=""Excel 12.0; HDR=YES; IMEX=1; ImportMixedTypes=Text; TypeGuessRows=0;""";
            OleDbCommand command = new OleDbCommand("SELECT * FROM [" + excelsheetname[0].ToString() + "]", conn);
            DataSet objDataSet = new DataSet();
            OleDbDataAdapter objDataAdapter = new OleDbDataAdapter();
            objDataAdapter.SelectCommand = command;
            objDataAdapter.Fill(objDataSet);
            GridViewExcel.DataSource = objDataSet.Tables[0].DefaultView;
            GridViewExcel.DataBind();
        }


        public bool IsUppercase(string str)
        {
            //variable to hold our return value
            bool upper;
            //variable to hold our search pattern
            string pattern = @"\b[A-Z]+\b";
   
         try
         {
            Regex AllCaps = new Regex(pattern);
            if (AllCaps.IsMatch(str))
            {
                upper = true;

            }
            else
            {

            upper = false;
            }
        }
            catch
        {
            upper = false;
        }
            return upper;
        }

        
        //import data into database
        protected void ImageButton3_Click(object sender, ImageClickEventArgs e)
        {
            lblduplicate.Visible = false;
            SqlConnection conn = new SqlConnection();
            SqlCommand comm = new SqlCommand();
            SqlCommand comm2 = new SqlCommand();
            SqlCommand checkcomm = new SqlCommand();
            checkcomm.Connection = conn;
            checkcomm.CommandText = "Select COUNT(*) From Wages WHERE Course =@Course AND Year = @Year";
            try
            {
                int importedCount = 0;
                ArrayList duplicateCourses = new ArrayList();
                int counts = 0;
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["dbConn"].ToString();
                conn.Open();

                comm2.Connection = conn;
                comm2.CommandText = "SELECT DISTINCT Year FROM Wages";
                ArrayList yearss = new ArrayList();
                SqlDataReader dr = comm2.ExecuteReader();
                while (dr.Read())
                {
                    yearss.Add(dr["Year"].ToString());
                }
                dr.Close();
                
                string Course = "";
                string[] year = GetExcelSheetNames(Server.MapPath("~/Saved.xls"));
                string t = RemoveSpecialCharacters(year);



                string PolyConducting = "";
                string FreshGradsEmpNum = "";
                string FreshGradsEmpRate = "";
                string FreshGradsMonthlyMean = "";
                string FreshGradsMonthlyMedian = "";
                string PostGradsEmpNum = "";
                string PostGradsEmpRate = "";
                string PostGradsMonthlyMean = "";
                string PostGradsMonthlyMedian = "";

           
                int count = GridViewExcel.Rows.Count;
                string[] coursedata;
                comm.Connection = conn;
                if (counts == 0)
                {
                    if (!yearss.Contains(t))
                    {

                        foreach (GridViewRow gvr in GridViewExcel.Rows)
                        {

                            Course = gvr.Cells[0].Text.Replace("&amp;", "&").Replace("&nbsp;", "nil").Replace("; ",";" );
                            bool result = IsUppercase (Course);
                            PolyConducting = gvr.Cells[1].Text.Replace("&nbsp;", "nil");
                            FreshGradsEmpNum = gvr.Cells[2].Text.Replace("&nbsp;", "nil").Replace(",","");
                            FreshGradsEmpRate = gvr.Cells[3].Text.Replace("&nbsp;", "nil").Replace(",", ""); ;
                            FreshGradsMonthlyMean = gvr.Cells[4].Text.Replace("&nbsp;", "nil").Replace(",", ""); ;
                            FreshGradsMonthlyMedian = gvr.Cells[5].Text.Replace("&nbsp;", "nil").Replace(",", ""); ;
                            PostGradsEmpNum = gvr.Cells[6].Text.Replace("&nbsp;", "nil").Replace(",", ""); ;
                            PostGradsEmpRate = gvr.Cells[7].Text.Replace("&nbsp;", "nil").Replace(",", ""); ;
                            PostGradsMonthlyMean = gvr.Cells[8].Text.Replace("&nbsp;", "nil").Replace(",", ""); ;
                            PostGradsMonthlyMedian = gvr.Cells[9].Text.Replace("&nbsp;", "nil").Replace(",", ""); ;
                            

                            coursedata = Course.Split(new char[] {';'});
                            foreach (string c in coursedata)
                            {
                                c.TrimStart();
                                checkcomm.Parameters.Clear();
                                checkcomm.Parameters.AddWithValue("@Course", c);
                                checkcomm.Parameters.AddWithValue("@Year", t);
                                int checkCount = (int)checkcomm.ExecuteScalar();

                                if (checkCount == 0)
                                {
                                    if (result == true)
                                    {
                                        comm.CommandText = " INSERT INTO Wages(Year, Course, PolyConducting, FreshGradsEmpNum, FreshGradsEmpRate, FreshGradsMonthlyMean, FreshGradsMonthlyMedian,PostGradsEmpNum, PostGradsEmpRate, PostGradsMonthlyMean, PostGradsMonthlyMedian, IsIndustrial) VALUES (@Year, @Course, @PolyConducting, @FreshGradsEmpNum, @FreshGradsEmpRate, @FreshGradsMonthlyMean, @FreshGradsMonthlyMedian, @PostGradsEmpNum, @PostGradsEmpRate, @PostGradsMonthlyMean, @PostGradsMonthlyMedian, @IsIndustrial)";
                                        comm.Parameters.AddWithValue("@Year", t);
                                        comm.Parameters.AddWithValue("@Course", c);
                                        comm.Parameters.AddWithValue("@PolyConducting", PolyConducting);
                                        comm.Parameters.AddWithValue("@FreshGradsEmpNum", FreshGradsEmpNum);
                                        comm.Parameters.AddWithValue("@FreshGradsEmpRate", FreshGradsEmpRate);
                                        comm.Parameters.AddWithValue("@FreshGradsMonthlyMean", FreshGradsMonthlyMean);
                                        comm.Parameters.AddWithValue("@FreshGradsMonthlyMedian", FreshGradsMonthlyMedian);
                                        comm.Parameters.AddWithValue("@PostGradsEmpNum", PostGradsEmpNum);
                                        comm.Parameters.AddWithValue("@PostGradsEmpRate", PostGradsEmpRate);
                                        comm.Parameters.AddWithValue("@PostGradsMonthlyMean", PostGradsMonthlyMean);
                                        comm.Parameters.AddWithValue("@PostGradsMonthlyMedian", PostGradsMonthlyMedian);
                                        comm.Parameters.AddWithValue("@IsIndustrial", "Is Industrial");
                                        comm.ExecuteNonQuery();

                                        comm.Parameters.Clear();
                                        importedCount++;
                                    }
                                    else {
                                        comm.CommandText = " INSERT INTO Wages(Year, Course, PolyConducting, FreshGradsEmpNum, FreshGradsEmpRate, FreshGradsMonthlyMean, FreshGradsMonthlyMedian,PostGradsEmpNum, PostGradsEmpRate, PostGradsMonthlyMean, PostGradsMonthlyMedian, IsIndustrial) VALUES (@Year, @Course, @PolyConducting, @FreshGradsEmpNum, @FreshGradsEmpRate, @FreshGradsMonthlyMean, @FreshGradsMonthlyMedian, @PostGradsEmpNum, @PostGradsEmpRate, @PostGradsMonthlyMean, @PostGradsMonthlyMedian, @IsIndustrial)";
                                        comm.Parameters.AddWithValue("@Year", t);
                                        comm.Parameters.AddWithValue("@Course", c);
                                        comm.Parameters.AddWithValue("@PolyConducting", PolyConducting);
                                        comm.Parameters.AddWithValue("@FreshGradsEmpNum", FreshGradsEmpNum);
                                        comm.Parameters.AddWithValue("@FreshGradsEmpRate", FreshGradsEmpRate);
                                        comm.Parameters.AddWithValue("@FreshGradsMonthlyMean", FreshGradsMonthlyMean);
                                        comm.Parameters.AddWithValue("@FreshGradsMonthlyMedian", FreshGradsMonthlyMedian);
                                        comm.Parameters.AddWithValue("@PostGradsEmpNum", PostGradsEmpNum);
                                        comm.Parameters.AddWithValue("@PostGradsEmpRate", PostGradsEmpRate);
                                        comm.Parameters.AddWithValue("@PostGradsMonthlyMean", PostGradsMonthlyMean);
                                        comm.Parameters.AddWithValue("@PostGradsMonthlyMedian", PostGradsMonthlyMedian);
                                        comm.Parameters.AddWithValue("@IsIndustrial", "Is Course");
                                        comm.ExecuteNonQuery();

                                        comm.Parameters.Clear();
                                        importedCount++;
                                    }
                                }
                                else
                                {
                                    duplicateCourses.Add(c);
                                }

                            }

                        }

                        lblImported.Text = String.Format("Data successfully imported into database (Imported: {0}, Duplicates: {1} (not imported)).", importedCount, duplicateCourses.Count);
                        lblImported.ToolTip = String.Format("Duplicates:\r\n{0}", duplicateCourses.Count > 0 ? String.Join("\r\n", duplicateCourses.ToArray()) : "None");
                        ImageButton3.Enabled = false;
                        ImageButton2.Enabled = false;

                    }
                    else
                    {
                        // lblImported.Visible = false;
                        lblduplicate.Text = "<Font color = RED>" + "Duplicate File Found, Please Select Another File to Upload! " + "</FONT>";
                        lblduplicate.Visible = true;
                    }
                    Debug.WriteLine("Sql Command Completed");
                }
            }
            catch (SqlException ex)
            {
                throw ex;
            }
            finally
            {
                conn.Close();
            }
            
           
        }

        protected void Button1_Click(object sender, EventArgs e)
        {
            /*SqlConnection conn = new SqlConnection();
            SqlCommand comm = new SqlCommand();

            try
            {
                string years = "";
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["dbConn"].ToString();
                conn.Open();

                comm.Connection = conn;
                comm.CommandText = "SELECT DISTINCT Year FROM Wages";
                SqlDataReader dr = comm.ExecuteReader();
                while (dr.Read()) 
                {
                    years = dr["Year"].ToString();
                }


            }
            catch (SqlException ex)
            {
                throw ex;
            }
            finally 
            {
                conn.Close();
            }*/
        }

    }
}
